





***************************************************************Part II: DealScan
{
***DealScan - Fullsample facilities
{ 
clear
cd "~/DataFolder"
use dealscan_facility.dta, clear

*Adjust facility start and end date
drop if facilitystartdate==. & facilityenddate==. & maturity==. 
replace facilitystartdate=facilityenddate-30*maturity if maturity!=. & facilityenddate!=. & facilitystartdate==.
replace facilityenddate=facilitystartdate+30*maturity if maturity!=. & facilityenddate==. & facilitystartdate!=.
replace maturity=int((facilityenddate-facilitystartdate)/30) if maturity==. & facilityenddate!=. & facilitystartdate!=.
drop if maturity<=0 
drop if facilitystartdate==. | facilityenddate==. | maturity==. 

gen facstryear=year(facilitystartdate)
gen facendyear=year(facilityenddate)
gen facstrmonth=month(facilitystartdate)
gen facendmonth=month(facilityenddate)

*sample starting from 1999
drop if facstryear<1987 

duplicates drop facilityid, force 

drop if missing(facilityamt) 
drop if facilityamt<=100000  

*rename
rename facilityid facid
rename borrowercompanyid bcoid

*Merge with Borrower linktable: link_borrower
merge 1:1 bcoid facid using link_toborrower
keep if _merge==3
drop _merge     

*Save
save dealscan_facility2, replace
}
*


***DealScan - Pricing

use dealscan_currentpricing.dta, clear // Downloaded from WRDS
drop if missing(allindrawn)
keep facilityid allindrawn
rename facilityid facid
duplicates drop facid, force
save dealscan_allindrawn.dta replace


***Dealscan Lenders
{

clear
cd "~/DataFolder"
use dealscan_lender, clear

rename companyid lenderid
rename facilityid facid
duplicates drop lenderid facid, force

***Define lead lenders by following Chakraborty et al. 2018 RFS to use the following ranking hierarchy: 
*1) lender is denoted as “Admin Agent”, 
*2) lender is denoted as “Lead bank”, 
*3) lender is denoted as “Lead arranger”, 
*4) lender is denoted as “Mandated lead arranger”, 
*5) lender is denoted as “Mandated arranger”, 
*6) lender is denoted as either “Arranger” or “Agent” and has a “yes” for the lead arranger credit, 
*7) lender is denoted as either “Arranger” or “Agent” and has a “no” for the lead arranger credit, 
*8) lender has a “yes” for the lead arranger credit but has a role other than those previously listed (“Participant” and “Secondary investor” are also excluded), 
*9) lender has a “no” for the lead arranger credit but has a role other than those previously listed (“Participant” and “Secondary investor” are also excluded), 
*10) lender is denoted as a “Participant” or “Secondary investor”.

gen lenderrole2=upper(lenderrole)
gen leadarrangercredit2=upper(leadarrangercredit)
bysort facid: egen numlenders=count(lenderid)

*1) sole lender
gen leadlender=1  if numlenders==1
replace leadlender=0 if missing(leadlender)
* 2) Admin Agent
bysort facid:  egen lead=sum(leadlender)
replace leadlender=1 if lead==0 & lenderrole2=="ADMIN AGENT"
* 3) Lead bank
bysort facid:  egen lead1=sum(leadlender)
replace leadlender=1 if lead1==0 & lenderrole2=="LEAD BANK"
* 4) Lead arranger
bysort facid:  egen lead2=sum(leadlender)
replace leadlender=1 if lead2==0 & lenderrole2=="LEAD ARRANGER"
* 5) Mandated lead arranger
bysort facid:  egen lead3=sum(leadlender)
replace leadlender=1 if lead3==0 & lenderrole2=="MANDATED LEAD ARRANGER"
* 6) Mandated arranger
bysort facid:  egen lead4=sum(leadlender)
replace leadlender=1 if lead4==0 & lenderrole2=="MANDATED ARRANGER"
* 7) “Arranger” or “Agent” and has a “yes” for the lead arranger credit
bysort facid:  egen lead5=sum(leadlender)
replace leadlender=1 if lead5==0 & (lenderrole2=="ARRANGER"  | lenderrole2=="AGENT") & (leadarrangercredit2=="YES")
* 8) lender is denoted as either “Arranger” or “Agent” and has a “no” for the lead arranger credit, 
bysort facid:  egen lead6=sum(leadlender)
replace leadlender=1 if lead6==0 & (lenderrole2=="ARRANGER"  | lenderrole2=="AGENT") & (leadarrangercredit2=="NO")
* 9) “lender has a “yes” for the lead arranger credit but has a role other than those previously listed (“Participant” and “Secondary investor” are also excluded), 
bysort facid:  egen lead7=sum(leadlender)
replace leadlender=1 if lead7==0 & leadarrangercredit2=="YES" & lenderrole2!="ADMIN AGENT" ///
    & lenderrole2!="LEAD BANK" & lenderrole2!="LEAD ARRANGER" & lenderrole2!="MANDATED LEAD ARRANGER" & lenderrole2!="MANDATED ARRANGER" & lenderrole2!="PARTICIPANT" & lenderrole2!="SECONDARY INVESTOR"
* 10)lender has a “no” for the lead arranger credit but has a role other than those previously listed (“Participant” and “Secondary investor” are also excluded), 	
bysort facid:  egen lead8=sum(leadlender)
replace leadlender=1 if lead8==0 & leadarrangercredit2=="NO" & lenderrole2!="ADMIN AGENT" ///
    & lenderrole2!="LEAD BANK" & lenderrole2!="LEAD ARRANGER" & lenderrole2!="MANDATED LEAD ARRANGER" & lenderrole2!="MANDATED ARRANGER" & lenderrole2!="PARTICIPANT" & lenderrole2!="SECONDARY INVESTOR"	
* 11) lender is denoted as a “Participant” or “Secondary investor”.
bysort facid:  egen lead9=sum(leadlender)
replace leadlender=1 if lead9==0 & (lenderrole2=="PARTICIPANT" | lenderrole2=="SECONDARY INVESTOR")

*only keep lead lenders;
keep if leadlender==1

*merge with facilities: dealscan_facility2
merge m:1 facid using dealscan_facility2
keep if _merge==3
drop _merge

*Check number of lead lenders in each facility
sort facid lenderid
bysort facid: gen count_temp=_N
sum count_temp, det 
drop if count_temp>10 // Drop loans with more than 10 lead lenders
replace facilityamt=facilityamt/count_temp // Divide the loan amount equally to lead lenders
drop count_temp

*merge with the Lender LinkTable: lenderid_rssdid_crosswalk
joinby lenderid using lenderid_rssdid_crosswalk_updated20220624   
keep if facstryear<=year & facendyear>=year

*Sort
keep rssdid year lenderid lender gvkey company bcoid facid numlenders ///
  facilityamt maturity facstryear facstrmonth facendyear facendmonth
order rssdid year lenderid lender gvkey company bcoid facid numlenders ///
  facilityamt facstryear facstrmonth facendyear facendmonth
format %30s lender
format %30s company

sort rssdid year lenderid bcoid facid
duplicates drop rssdid year lenderid bcoid facid, force  

merge m:1 facid using dealscan_allindrawn.dta
drop if _merge==2
drop _merge

save dealscan_facility3, replace
}
*
}
*


